<?php

/**
  Database library for 4X_Pro's Intellect Framework
  @package INTBF
  @author 4X_Pro <admin@openproj.ru>
  @version 1.0
  @copyright 2006-2009, 4X_Pro
  @url http://www.openproj.ru
**/

define('DB_ERROR_SILENT',0);
define('DB_ERROR_MASK',1);
define('DB_ERROR_ALL',2);

define('DB_LOCK_READ',1);
define('DB_LOCK_WRITE',2);

/**  Common abstract class for dealing with database. All RDBMS-specific classes
*    should be this class descendants.
*    @package MySQL3
*    @abstract
     **/

class Database {
/**  @access private
*    @var resource
*    Link for database connection
**/
  var $link;

/** @access private
    @var array
    Connection parameters: login, password, host, etc. **/
  var $params;

/** @access private
    @var integer
    Number of executed queries. **/
  var $query_count;

/** @access private
    @var float
    Number of time elapsed for queries. **/
  var $query_time;

/** @access private
    @var float
    If query executed more than specified number of seconds, the notice will be generated and logged. **/
  var $query_long_time;
  
/** The number of rows in last successful SELECT query **/ 
  var $num_rows;

  function slashes($text) {
    return addslashes($text);
  }

/** Creates a connection to a database sever (Really this function only takes parameters.
Connection is opened only when first query is requested.)
*  @return boolean
*  @param mixed $filename May be name of include-file or hash array with parameters of connection.
*  Parameters of connection are:<ul>
*  <li>DBhost -- name of database server
*  <li>DBname -- name of database;
*  <li>DBuser -- name of user;
*  <li>DBpassword -- the password;
*  </ul>
*  In case of hash the parameters should be it's keys,
*  in case of include file the params must be defined as variables in the file.
**/
  function connect($params="config/database.php") {
    $this->query_time=0.0;
    $this->query_count=0;
    $this->query_long_time=2.0;
    if (is_array($params)) $this->params=$params;
    else trigger_error('Некорректно заданы параметры подключения к БД!',E_USER_ERROR);
  }

/** This function really establish connection. It should never called directly
**/
  function _connect() {}

/** Closes a connection to database server, opened with {@see function connect()}
* @return boolean
**/
  function close() {}

/** Defines the character set for connection to database server.
*  @param string $charset Character set for
*  @return boolean
**/
  function set_charset($charset) {}

/** Gets database server software version
*   @return string Version number of RDBMS.
**/
  function get_version() {}

/** Execute SELECT query and return all fetched rows as array, each row is hash indexed by field names.
*  @return array
*  @param string $sql SQL query to execute
*  @param integer $limit Start value for SQL LIMIT clause
*  @param integer $count Number of values to fetch in SQL LIMIT clause
**/
  function &select_all($sql,$limit=false,$count=false) {
    $sql = $this->add_limiter($sql,$limit,$count);
    $res = $this->query($sql);
    $this->num_rows=$this->_num_rows($res);
    $result = array();
    while ($data=$this->fetch_array($res)) {
      $result[]=$data;
    }
    $this->free_res($res);
    return $result;
  }
  
/** Execute SELECT query and return all fetched rows as array, each row is array 
* (the index of field is the number of its position in query).
*  @return array
*  @param string $sql SQL query to execute
*  @param integer $limit Start value for SQL LIMIT clause
*  @param integer $count Number of values to fetch in SQL LIMIT clause
**/
  function &select_all_rows($sql,$limit=false,$count=false) {
    $sql = $this->add_limiter($sql,$limit,$count);
    $res = $this->query($sql);
    $this->num_rows=$this->_num_rows($res);
    $result = array();
    while ($data=$this->fetch_row($res)) {
      $result[]=$data;
    }
    $this->free_res($res);
    return $result;
  }
  
/** Execute SELECT query and return all fetched rows as array, each row is array 
* (the index of field is the number of its position in query).
*  @return array
*  @param string $sql SQL query to execute
*  @param integer $limit Start value for SQL LIMIT clause
*  @param integer $count Number of values to fetch in SQL LIMIT clause
**/
  function &select_all_numbers($sql,$limit=false,$count=false) {
    $sql = $this->add_limiter($sql,$limit,$count);
    $res = $this->query($sql);
    $this->num_rows=$this->_num_rows($res);
    $result = array();
    while ($data=$this->fetch_row($res)) {
      $result[]=intval($data[0]);
    }
    $this->free_res($res);
    return $result;
  }
  
/** Execute SELECT query and return all fetched rows as array, each row is array 
* (the index of field is the number of its position in query).
*  @return array
*  @param string $sql SQL query to execute
*  @param integer $limit Start value for SQL LIMIT clause
*  @param integer $count Number of values to fetch in SQL LIMIT clause
**/
  function &select_all_strings($sql,$limit=false,$count=false) {
    $sql = $this->add_limiter($sql,$limit,$count);
    $res = $this->query($sql);
    $this->num_rows=$this->_num_rows($res);
    $result = array();
    while ($data=$this->fetch_row($res)) {
      $result[]=$data[0];
    }
    $this->free_res($res);
    return $result;
  }

/** Execute SELECT query and return all fetched rows as hash, and builds
*   a hash of hashes where indexes is specified in $key column and
*   other columns are array members of each hash
* (the index of field is the number of its position in query).
*  @return array
*  @param string $sql SQL query to execute
*  @param integer $limit Start value for SQL LIMIT clause
*  @param integer $count Number of values to fetch in SQL LIMIT clause
**/
  function &select_hash($sql,$key,$limit=false,$count=false) {
    $data=$this->select_all($sql,$limit,$count);
    $result=false;
    if ($data) {
      for ($i=0, $count=count($data); $i<$count; $i++) $result[$data[$i][$key]]=$data[$i];
    }
    return $result;
  }
  
/** Execute SELECT query and return all fetched rows as hash, and builds
*   a hash of hashes where indexes is specified in $key column and
*   other columns are array members of each hash
* (the index of field is the number of its position in query).
*  @return array
*  @param string $sql SQL query to execute
*  @param integer $limit Start value for SQL LIMIT clause
*  @param integer $count Number of values to fetch in SQL LIMIT clause
**/
  function &select_simple_hash($sql,$limit=false,$count=false) {
    $data=$this->select_all_rows($sql,$limit,$count);
    $result=false;
    if ($data) {
      for ($i=0, $count=count($data); $i<$count; $i++) $result[$data[$i][0]]=$data[$i][1];
    }
    return $result;
  }

/** Adds RDBMS-specific LIMIT clause for SQL operator.
*   @return string Modified SQL operator
*   @param string $sql SQL operator
*   @param $limit1 The first parameter of LIMIT
*   @param $limit2 The second parameter of LIMIT
**/
  function add_limiter($sql,$limit1=false,$limit2=false) {
  }

/** Execute SELECT query for fetching only one row and return hash indexed by field names.
*  @return array
*  @param string $sql SQL query to execute
**/
  function &select_row($sql) {
    $sql=$this->add_limiter($sql,1);
    $res=$this->query($sql);
    $this->num_rows=$this->_num_rows($res);
    $result=$this->fetch_array($res);
    $this->free_res($res);
    return $result;
  }

/** Execute SELECT query for fetching integer value.
*  @return integer
*  @param string $sql SQL query to execute.
**/
  function select_int($sql) {
    $sql=$this->add_limiter($sql,1);
    $res=$this->query($sql);
    $this->num_rows=$this->_num_rows($res);
    list($result)=$this->fetch_row($res);
    $this->free_res($res);
    return intval($result);
 }

/** Execute SELECT query for fetching integer value.
*  @return array
*  @param string $sql SQL query to execute.
**/
  function &select_str($sql) {
    $sql=$this->add_limiter($sql,1);
    $res=$this->query($sql);
    $this->num_rows=$this->_num_rows($res);
    list($result)=$this->fetch_row($res);
    $this->free_res($res);
    return $result;
  }

/** Execute query of any type except SELECT and mark
*  @return mixed FALSE if there were errors, number of affected rows -- if successful
*  @param string $sql SQL query to execute.
**/
  function query($sql) {
    global $app;
    $long_time=isset($GLOBALS['debug']['query_long_time']) ? $GLOBALS['debug']['query_long_time'] : 3600;
    $dbg=isset($GLOBALS['debug']['debug_sql']) ? $GLOBALS['debug']['debug_sql'] : 0;
    if (!$this->link) $this->_connect();
    $start_time=array_sum(explode(' ',microtime()));
    $res=$this->_query($sql);
		if ($res==false) {
			_dbg($sql);
			trigger_error('Ошибка в SQL-запросе ('.$this->error_num().'): '.$this->error_str(),E_USER_ERROR);
		}
    $this->query_count++;
    $q_time=array_sum(explode(' ',microtime()))-$start_time;
    $this->query_time+=$q_time;
    if ($dbg>=1) {
      _dbg($sql.' Exec time:'.$q_time);
    }
    if ($q_time>$long_time && ($dbg>=2)) {
      _dbg($this->explain($sql));
    }
    return $res;
  }

/** Do real query execution
*  @return mixed FALSE if there were errors, number of affected rows -- if successful
*  @param string $sql SQL query to execute.
**/
  function _query($sql) {
    trigger_error(MSG_db_noquery_overloaded,E_USER_ERROR);
  }

/** Return error code from last query.
*  @param only_errors TRUE if warnings and notices should not be returned
*  @return intger;
**/
  function error_num($only_errors=false) {
  }

/** Return error message from last query.
*  @return string;
**/
  function error_str() {
  }

/** Use RBMSs EXPLAIN-like operator to describe, how query will be executed.
* Should be overriden in descendatns
* @param string $sql SQL query text
* @return string Explain of query execution
**/
  function explain($sql) {
  }

/** Fetches associative array from query result.
    Should be overriden in descendatns
    @param resource $res Query result returned.
    @return array Hash of fields from current row.
**/
  function fetch_array(&$res) {
  }

/** Fetches array from query result.
    Should be overriden in descendatns
    @param resource $res Query result returned.
    @return array Array of fields from current row.
**/
  function fetch_row(&$res) {
  }


/** Stores given data hash in specified table.
*  @return boolean FALSE if there were errors
*  @param array &$data Data array. Indexes of elements should match field names in table.
*  Table should have field $prefix_id, where $prefix is second function parameter,
*  which should be autoincrement field.
*  If $data do not contain element id, INSERT query will be executed
*  and field id should be set to inline(@see fucntion insert_id()} value.
*  If id element is set the UPDATE query will be executed with
*  WHERE id=$data['id'] condition.
*  @param string  $table Table to store data.
*  @param string  $prefix Only keys started with specified prefix would be stored.
*  @param string  $id_column Name of column (and array key) which contains the unique ID of data row
*  @param boolean $ignore If TRUE, the store function works as REPLACE operator in MySQL
*  @param string  $key Comma-separated names of key fields for INSERT IGNORE
*  This is handy to store fields to different tables according their prefixes
**/
/*  function store(&$data,$table,$prefix,$id_column=false,$ignore=false,$key=NULL) {
    if (!$id_column)  $id_column=$prefix.'id';
    $result=false;
    if (isset($data[$id_column]) && $data[$id_column]) { 
      if (!$this->store_update($data,$table,$prefix,$id_column,$id_column.'='.$data[$id_column])) {
        $this->store_insert($data,$table,$prefix,$id_column,true);
      }
    }
    else $this->store_insert($data,$table,$prefix,$id_column,$ignore,$key);
  }*/

/** UPDATE query for saving data in specified table.
*  Called from store, so developers are encouraged to use store instead.
*  @return boolean
*  @param string $table Table to store data.
*  @param string $prefix Only keys started with specified prefix would be stored.
*  @param string $id_column Name of column (and array key) which contains the unique ID of data row
*  @param boolean $ignore If TRUE, the store function works as REPLACE operator in MySQL
*  @param string $condition Strings, which specifies WHERE part of SQL query
**/
/*  function store_update(&$data,$table,$prefix,$id_column,$condition) {
    if (!$id_column)  $id_column=$prefix.'id';
    $sqlarray=array();
    $len = strlen($prefix);
    foreach ($data as $key=>$value) {
      if ($key!=$id_column && substr($key,0,$len)==$prefix  && substr($key,$len,1)!='_')
        $sqlarray[]=$key.'="'.addslashes($value).'"';
    }
    if (count($sqlarray)) {
      $sql = 'UPDATE '.$table.' SET '.join(', ',$sqlarray).' WHERE '.$condition;
      $this->query($sql);
      return $this->affected_rows();
    }
  }*/

/** Выполнение SQL-оператора INSERT над массивом с данными 
*  This function should not be called directly, use store instead.
*  @return boolean
*  @param string $table Table to store data.
**/
  function insert($table,$data,$ignore=false) {
    $sqlarray1=array();
    $sqlarray2=array();
    foreach ($data as $key=>$value) {
        $sqlarray1[]='`'.$key.'`';
        $sqlarray2[]='"'.addslashes($value).'"';
    }
    if (count($sqlarray1)) {
      $sql = "INSERT INTO $table (".join(', ',$sqlarray1).') VALUES  ('.join(', ',$sqlarray2).')';
      $this->query($sql);
      return $this->insert_id();
    }
		else trigger_error('Нет данных для записи!',E_USER_ERROR);
  }

/** Converts INSERT operator into INSERT IGNORE. Database-specific, should be overriden in descendants.
*  Called from store when $ignore is TRUE.
*  @return string SQL with INSERT IGNORE
*  @param string $sql SQL operator with INSERT
**/
  function insert_ignore($sql) {}

/** Выполнение запроса UPDATE **/	
	function update($table,$data,$condition) {
		$sqlarray=array();
		foreach ($data as $key=>$value) $sqlarray[]='`'.$this->slashes($key).'`'.'="'.$this->slashes($value).'"';
    if (!empty($sqlarray)) {
      $sql = "UPDATE $table SET ".join(',',$sqlarray).' WHERE '.$condition;
      $this->query($sql);
    }
		else trigger_error('Нет данных для записи!',E_USER_ERROR);		
	}
	
/** Выполнение запроса REPLACE (или его аналога, если в СУБД он не предусмотрен) **/
	function replace($table,$data,$condition) {
	}

/** Return number of rows in last successful SELECT query.
* @return integer The number of rows.
**/
  function num_rows() { return $this->num_rows; }

/** Fetches and stores number of rows after SELECT query call.
* Should be overriden in descendants
* @return integer The number of rows.
**/
  function _num_rows(&$res) {}

/** Return number of rows affected last query (except INSERT).
* @return integer The number of rows.
**/
  function affected_rows() {}

/** Return auto-incement value of the first row, inserted by last INSERT
* @return integer The last auto-increment value.
**/
  function insert_id() {}

/** Request to RDBMS to analyze table indexes for possible performance improvement
* @return boolean TRUE if successful
* @param mixed $tables String with single table name or array of table names to analyze.
**/
  function analyze($tables) {}

/** Request to RDBMS to omptimize table and totally remove deleted rows
* @return boolean TRUE if successful
* @param mixed $tables String with single table name or array of table names to optimize.
**/
  function optimize($tables) {}

/** Request to RDBMS to repair table and fix damaged indexes
* @return boolean TRUE if successful
* @param mixed $tables String with single table name or array of table names to repair.
**/
  function repair($tables) {}

/** Get list of all fields from specified table.
* @return array The list of all fields in table (only names).
* @param string $table Table name.
**/
  function get_fields($table) {}

/** Get info about all fields from specified table.
* @return array The list of all fields in table, each element of array is hash containing keys: <ul>
<li>
<li>
</ul>
* @param string $table Table name.
**/
  function list_fields($table) {}

/** Get list of all tables from current database.
* @return array The list of all tables (only names).
**/
  function get_tables($prefix) {}

/** Get info of all tables from current database.
* @param string prefix Prefix with which the names of the tables should start.
* @return array The list of all tables, each element of array is hash containing keys: <ul>
<li>
<li>
</ul>
**/
  function list_tables($prefix) {}

/** Get info about all fields from specified table.
* @param string prefix Prefix with which the names of the tables should start.
* @return array The list of all fields in table, each element of array is hash containing keys: <ul>
<li>
<li>
</ul>
* @param string $table Table name.
**/
  function list_indexes($table) {}

/** Gets a total size of all tables with specified prefix.
* @return integer The total size n bytes
* @param string prefix Prefix with which the names of the tables should start.
**/
  function get_db_size($prefix) {}

/** Locks tables for write or for read
* @return boolean TRUE if successful
* @param mixed $tables String with single table name or array of table names to lock.
* @param integer $mode Mode for locking. May be one of this values: <ul>
* <li>DB_LOCK_READ -- locking for reading
* <li>DB_LOCK_WRITE -- locking for writing</ul>
**/
  function lock_tables($tables,$mode) {}

/** Unlocks previously locked tables
* @return boolean TRUE if successful
* @param mixed $tables String with single table name or array of table names to repair.
**/
  function unlock_tables($tables) {}

/** Transaction start for RBDMS with transactional support
* @return boolean TRUE if successful
**/
  function begin() {}

/** Transaction commit for RBDMS with transactional support
* @return boolean TRUE if successful
**/
  function commit() {}

/** Transaction abort for RBDMS with transactional support
* @return boolean TRUE if successful
**/
  function rollback() {}

/** Connect to specified server and attempts to crate database if not exists.
* @return boolean TRUE if successful or database already exists
* @param string $name The name of database to create
* @param mixed $params The parameters for connection to database. See connect for its meaning.
**/
  function db_create($params,$name) {}

/** Checks whether user has enough rights to create tables and indexes
* and execute INSERT, UPDATE, SELECT and DELETE data
* @return boolean TRUE if successful or database already exists, FALSE if there no such database and unable to create it.
* @param string $name The name of database to create
**/
  function check_access() {}

/** Makes dump of specified tables to file.
* @return boolean TRUE if successful
* @param mixed $tables Can be one of following: <ul>
* <li>Single table name
* <li>Array of table names
* <li>Array of hashes, each containing two keys: <ul>
* <li>Name -- the name of the table
* <li>Where -- conditions for WHERE-part of query
* </ul></ul>
* @param string $filename File to save the dump
**/
  function dump_tables($tables,$filename) {}

/** Changing table structure to match specified in file (but does not delete unspecified rows)
* Useful for updating versions
* @return boolean TRUE if successful
* @param mixed $tables Can be one of following: <ul>
* <li>Single table name
* <li>Array of table names
* </ul>
* @param string $filename File to read table definition
**/
  function build_tables($tables) {}

/** Checks whether RDBMS supports stored procedures
* @return boolean
**/
  function has_stored_proc() {}

/** Checks whether RDBMS supports subqueries and UNION operator
* @return boolean
**/
  function has_subqueries() {
  }

/** Checks whether RDBMS supports transactions
* @return boolean
**/
  function has_transactions() {}

  /** Преобразует PHP-массив в часть SQL-запроса, которая может быть безопасно помещена в условие WHERE
   *
   * @param mixed $data Массив параметров или строка со списком параметров, разделенных запятыми
   */
  function array_to_sql($data,$column) {
    $sqldata='`'.$this->slashes($column).'` IN (';
    if (!is_array($data)) $data=explode(',',$data); // если пришла строка, а не массив, разбиваем ее по запятым и превращаем в массив все равно
    for ($i=0, $count=count($data); $i<$count; $i++) {
      if ($i>0) $sqldata.=',';
      $sqldata.='"'.$this->slashes($data[$i]).'"';
    }
    if ($count==0) $sqldata=' 0=1'; // если массив пуст, то возвращаем условие, которое всегда ложно
    else $sqldata.=')';
    return $sqldata;
  }
}

